package com.cmnit.analysis.dao

import com.cmnit.analysis.common.{TDao, TSql}
import org.apache.log4j.Logger
import org.apache.spark.sql.DataFrame

class TrafficOutsideDao extends TSql with TDao {
  private val logger: Logger = Logger.getLogger(this.getClass)

  /**
   * 获取省外出口流量（结果表）
   *
   * @return
   */
  def trafficOutside(enTime: String, exTime: String, acctDate: String): DataFrame = {
    sparkSQL(
      "SELECT " +
        "a.entollstationid enid, " +
        "a.tollstationname enname, " +
        "'station' entype, " +
        "b.gantryid gantryid, " +
        "b.gantryname gantryname, " +
        "b.vehicletype vehicletype, " +
        "COUNT(*) vehiclecount, " +
        "'" + acctDate + "' as statisDay " +
        "FROM " +
        "(SELECT " +
        "distinct a.passid, " +
        "a.entollstationid, " +
        "b.tollstationname " +
        "FROM " +
        "ods.ods_etc_tollenbillinfo a " +
        "LEFT JOIN " +
        "tmp_station_info b " +
        "ON a.entollstationid = b.gbtollstationid " +
        "WHERE "
        + enTime +
        "AND b.tollstationname NOT LIKE '%九江一桥%' " +
        "AND a.transCode in ('0101','0103','0106')) a " +
        "JOIN " +
        "(SELECT " +
        "distinct a.gantryid, " +
        "a.vehicletype, " +
        "a.passid, " +
        "b.gantryname " +
        "FROM " +
        "ods.ods_etc_gantryetcbill a " +
        "LEFT JOIN " +
        "tmp_organization_gantryinfo b " +
        "ON a.gantryid = b.gantryid " +
        "WHERE "
        + exTime +
        "AND a.gantrytype = '3' " +
        "AND ((a.tradeResult='0' and a.mediaType='2') or (a.OBUTradeResult='0' and a.mediaType='1'))) b " +
        "ON " +
        "a.passid = b.passid " +
        "GROUP BY a.entollstationid,a.tollstationname,b.gantryid,b.gantryname,b.vehicletype " +
        "UNION " +
        "SELECT " +
        "a.gantryid enid, " +
        "a.gantryname enname, " +
        "'gantry' entype, " +
        "b.gantryid gantryid, " +
        "b.gantryname gantryname, " +
        "b.vehicletype vehicletype, " +
        "COUNT(*) vehiclecount, " +
        "'" + acctDate + "' as statisDay " +
        "FROM " +
        "(SELECT " +
        "distinct a.gantryid, " +
        "a.passid, " +
        "b.gantryname " +
        "FROM " +
        "ods.ods_etc_gantryetcbill a " +
        "LEFT JOIN " +
        "tmp_organization_gantryinfo b " +
        "ON a.gantryid = b.gantryid " +
        "WHERE "
        + enTime +
        "AND a.gantrytype = '2' " +
        "AND ((a.tradeResult='0' and a.mediaType='2') or (a.OBUTradeResult='0' and a.mediaType='1'))) a " +
        "JOIN " +
        "(SELECT " +
        "distinct a.gantryid, " +
        "a.vehicletype, " +
        "a.passid, " +
        "b.gantryname " +
        "FROM " +
        "ods.ods_etc_gantryetcbill a " +
        "LEFT JOIN " +
        "tmp_organization_gantryinfo b " +
        "ON a.gantryid = b.gantryid " +
        "WHERE "
        + exTime +
        "AND a.gantrytype = '3' " +
        "AND ((a.tradeResult='0' and a.mediaType='2') or (a.OBUTradeResult='0' and a.mediaType='1'))) b " +
        "ON " +
        "a.passid = b.passid " +
        "GROUP BY a.gantryid,a.gantryname,b.gantryid,b.gantryname,b.vehicletype "
    )
  }
}
